import datetime, exceptions, logging
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class AuthorDim(Base):
    __tablename__ = 'd_authors'
    id  = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, unique=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Author(%s)>" % self.name

# date dimension part

class DaySubDim(Base):
    __tablename__ = 'sd_days'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 1 AND 31'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<DaySubDim(value=%s)>" % self.value

class MonthSubDim(Base):
    __tablename__ = 'sd_months'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 1 AND 12'), nullable=False, unique=True)
    name  = Column(String(16), nullable=False, unique=True)

    def __init__(self, value, name):
        self.value = value
        self.name  = name

    def __repr__(self):
        return "<MonthSubDim(value=%s, name=%s)>" % (self.value, self.name)

class YearSubDim(Base):
    __tablename__ = 'sd_years'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value > 2000'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<YearSubDim(value=%s)>" % self.value

class WeekSubDim(Base):
    __tablename__ = 'sd_weeks'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 1 AND 53'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<WeekSubDim(value=%s)>" % self.value

class DayOfWeekSubDim(Base):
    __tablename__ = 'sd_week_days'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 1 AND 7'), nullable=False, unique=True)
    name  = Column(String(16), nullable=False, unique=True)

    def __init__(self, value, name):
        self.value = value
        self.name  = name

    def __repr__(self):
        return "<DayOfWeekSubDim(value=%s, name=%s)>" % (self.value, self.name)

class DayOfYearSubDim(Base):
    __tablename__ = 'sd_year_days'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 1 AND 366'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<DayOfYearSubDim(value=%s)>" % self.value

class DateDim(Base):
    __tablename__ = 'd_dates'

    id    = Column(Integer, primary_key=True)
    date  = Column(Date, nullable=False, unique=True)

    day_id       = Column(Integer, ForeignKey('sd_days.id'), nullable=False)
    day          = relation(DaySubDim, backref=backref('dates'))

    month_id     = Column(Integer, ForeignKey('sd_months.id'), nullable=False)
    month        = relation(MonthSubDim, backref=backref('dates'))

    year_id      = Column(Integer, ForeignKey('sd_years.id'), nullable=False)
    year         = relation(YearSubDim, backref=backref('dates'))

    week_id      = Column(Integer, ForeignKey('sd_weeks.id'), nullable=False)
    week         = relation(WeekSubDim, backref=backref('dates'))

    dayOfWeek_id = Column(Integer, ForeignKey('sd_week_days.id'), nullable=False)
    dayOfWeek    = relation(DayOfWeekSubDim, backref=backref('dates'))

    dayOfYear_id = Column(Integer, ForeignKey('sd_year_days.id'), nullable=False)
    dayOfYear    = relation(DayOfYearSubDim, backref=backref('dates'))

    def __init__(self, year, month, day, week, dayOfWeek, dayOfYear):
        self.date      = datetime.date(year.value, month.value, day.value)
        self.year      = year
        self.month     = month
        self.day       = day
        self.week      = week
        self.dayOfWeek = dayOfWeek
        self.dayOfYear = dayOfYear

    def __repr__(self):
        return "<DateDim(%s-%s-%s %s)>" % (self.year, self.month, self.day, self.week)

d_dates_table = DateDim.__table__
d_dates_table.append_constraint(UniqueConstraint('day_id', 'month_id', 'year_id'))


# time dimension part

class HourSubDim(Base):
    __tablename__ = 'sd_hours'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 0 AND 23'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<HourSubDim(value=%s)>" % self.value

class MinuteSubDim(Base):
    __tablename__ = 'sd_minutes'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 0 AND 59'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<MinuteSubDim(value=%s)>" % self.value

class SecondSubDim(Base):
    __tablename__ = 'sd_seconds'
    id    = Column(Integer, primary_key=True)
    value = Column(Integer, CheckConstraint('value BETWEEN 0 AND 59'), nullable=False, unique=True)

    def __init__(self, value):
        self.value = value

    def __repr__(self):
        return "<SecondSubDim(value=%s)>" % self.value

class TimeDim(Base):
    __tablename__ = 'd_times'

    id   = Column(Integer, primary_key=True)
    time = Column(Time, nullable=False, unique=True)

    hour_id   = Column(Integer, ForeignKey('sd_hours.id'), nullable=False)
    hour      = relation(HourSubDim, backref=backref('times'))

    minute_id = Column(Integer, ForeignKey('sd_minutes.id'), nullable=False)
    minute    = relation(MinuteSubDim, backref=backref('times'))

    second_id = Column(Integer, ForeignKey('sd_seconds.id'), nullable=False)
    second    = relation(SecondSubDim, backref=backref('times'))

    def __init__(self, hour, minute, second):
        self.time   = datetime.time(hour.value, minute.value, second.value)
        self.hour   = hour
        self.minute = minute
        self.second = second

    def __repr__(self):
        return "<TimeDim(%s:%s:%s)>" % (self.hour.value, self.minute.value, self.second.value)

d_times_table = TimeDim.__table__
d_times_table.append_constraint(UniqueConstraint('hour_id', 'minute_id', 'second_id'))

# facts

class ChangeFact(Base):
    __tablename__ = 'f_changes'
    id  = Column(Integer, primary_key=True)

class RevisionFact(Base):
    __tablename__ = 'f_revisions'
    id  = Column(Integer, primary_key=True)

    date_id = Column(Integer, ForeignKey('d_dates.id'), nullable=False)
    date    = relation(DateDim, backref=backref('revisions'))

    time_id = Column(Integer, ForeignKey('d_times.id'), nullable=False)
    time    = relation(TimeDim, backref=backref('revisions'))

    author_id = Column(Integer, ForeignKey('d_authors.id'), nullable=False)
    author    = relation(AuthorDim, backref=backref('revisions'))

    def __init__(self, date, time, author):
        self.date   = date
        self.time   = time
        self.author = author

    def __repr__(self):
        return "<RevisionFact(%s, %s, %s)>" % (self.date, self.time, self.author)

# schema import helpers

month_names = [ 'January',   'February',    'March',     'April',
                'May',       'June',        'July',      'August',
                'September', 'October',     'November',  'December' ]

week_days_names = [ 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' ]

def prepare_schema(session):
    if session is None:
        raise exceptions.AttributeError('Session must be provided')
    Base.metadata.create_all(session.bind)    

def import_time_dimension(session):
    """Imports time dimension data into DB using given non-transactional Session object.
    Takes a longer while, it loads 24 HourSubDim, 60 MinuteSubDim, 60 SecondSubDim and 86400 (24*60*60) TimeDim objects.
    """
    if session is None:
        raise exceptions.AttributeError('Session must be provided')

    logging.info('Preparing time dimension')

    hours   = map(lambda x: HourSubDim(x), range(0,24))
    minutes = map(lambda x: MinuteSubDim(x), range(0,60))
    seconds = map(lambda x: SecondSubDim(x), range(0,60))

    session.add_all(hours)
    session.add_all(minutes)
    session.add_all(seconds)
    session.flush()

    for h in hours:
        times = list()
        for m in minutes:
            for s in seconds:
                times.append(TimeDim(h,m,s))
        session.add_all(times)
        session.flush()
        logging.debug('Finished hour %s of %s' % (h.value+1, len(hours)))

    logging.info('Finished time dimension')

def import_date_dimension(session, yearsToImport):
    """
    """
    if session is None:
        raise exceptions.AttributeError('Session must be provided')

    if yearsToImport is None:
        raise exceptions.AttributeError('Single year or list of years are required')

    if type(yearsToImport) is int:
        yearsToImport = [yearsToImport]

    logging.info('Preparing date sub-dimensions')

    days      = map(lambda x: DaySubDim(x), range(1, 32))
    months    = map(lambda x,y: MonthSubDim(x,y), range(1,13), month_names)
    years     = map(lambda x: YearSubDim(x), yearsToImport)
    weeks     = map(lambda x: WeekSubDim(x), range(1, 54))
    week_days = map(lambda x,y: DayOfWeekSubDim(x,y), range(1, 8), week_days_names)
    year_days = map(lambda x: DayOfYearSubDim(x), range(1, 367))

    session.add_all(days)
    session.add_all(months)
    session.add_all(years)
    session.add_all(weeks)
    session.add_all(week_days)
    session.add_all(year_days)
    session.flush()

    weeks     = dict(zip(range(1,54), weeks))
    week_days = dict(zip(range(1,8), week_days))
    year_days = dict(zip(range(1,367), year_days))

    logging.info('Preparing date dimension')

    for y in years:
        for m in months:
            dates = list()
            for d in days:
                try:
                    date = datetime.date(y.value, m.value, d.value)
                    iso = date.isocalendar()
                    dd = DateDim(y,m,d, weeks[iso[1]], week_days[iso[2]], year_days[date.timetuple()[7]])
                    dates.append(dd)
                except ValueError, e:
                    pass
                except KeyError, e:
                    print e
            session.add_all(dates)
            session.flush()
        logging.debug('Finished year %s' % y.value)

    logging.info('Finished date dimension')
